import dfbz.com.dao.base.BaseDao;
import dfbz.com.model.Dept;
import dfbz.com.model.User;
import dfbz.com.model.UserFocus;
import dfbz.com.utils.JDBCUtil;
import dfbz.com.utils.MailUtil;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.junit.Test;

import javax.mail.MessagingException;
import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class test1 {

    public static void main(String[] args) {
        System.out.println("test test");
    }


    @Test
    public void test1() {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            User user = qr.query("select * from login where username = ? limit 1", new BeanHandler<>(User.class), "admin");
            System.out.println(user);
        } catch (Exception e) {
            e.printStackTrace();
            e.getMessage();
        }
    }


    @Test

    public void test2() {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        String sql = "select * from user where username = 'admin' limit 1 ";

        try {
            connection = JDBCUtil.getConnection();
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();

            if (resultSet.next()) {
                int id = resultSet.getInt("id");
                String un = resultSet.getString("username");
                String password = resultSet.getString("password");
                String is_secret = resultSet.getString("is_secret");
                String email = resultSet.getString("email");
                int dept_id = resultSet.getInt("dept_id");

                User user = new User();
                user.setPassword(password);
                user.setDeptId(id);
                user.setUsername(un);
                user.setIsSecret(is_secret);
                user.setEmail(email);
                user.setDeptId(dept_id);
                System.out.println(user);
            } else {
                System.out.println("找不到用户");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, statement, connection);
        }

    }

    @Test
    public void test3() {
        BaseDao<Object> bas = new BaseDao<>();
        List<Object> objects = bas.addAll(User.class);
        for (Object object : objects) {
            System.out.println(object);
        }
    }

    @Test
    public void test4() {
        String msg = "1dasd23424@qq.cmn";
        System.out.println(isEmail(msg));
    }


    private boolean isEmail(String email) {
        if (null == email || "".equals(email)) {
            return false;
        }
        String regEx1 = "^([a-z0-9A-Z]+[-|\\.]?)+[a-z0-9A-Z]@([a-z0-9A-Z]+(-[a-z0-9A-Z]+)?\\.)+[a-zA-Z]{2,}$";
        Pattern p = Pattern.compile(regEx1);
        Matcher m = p.matcher(email);
        if (m.matches()) {
            return true;
        } else {
            return false;
        }
    }


    //    public static void main(String[] args) {
//        String msg = "1dasd23424@qq。cmn";
//        System.out.println(isEmail(msg));
//    }

    @Test
    public void method5() {
        String email = "2035311573@qq.com";
        try {
            MailUtil.send(email, "测试 测试", "test test！！", MailUtil.UNICODE);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    @Test
    public void test6() {

        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        //下划线转驼峰形式---下划线的形式都转成驼峰形式
        BasicRowProcessor bp = new BasicRowProcessor(new GenerousBeanProcessor());
        try {
            User admin = qr.query("select * from login where username = ? limit 1", new BeanHandler<>(User.class, bp), "admin");
            System.out.println(admin);

        } catch (SQLException e) {
            e.printStackTrace();
            e.getMessage();
        }

    }

    @Test
    public void getDetail() {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        Map<String, Object> map = null;
        try {
            String sql = "select ui.user_id as userId,ui.real_name as realName,ui.age,ui.gender,u.is_secret as isSecret," +
                    "ui.phone,ui.describe as introduce,ui.register_time as registerTime,ui.login_time as loginTime," +
                    "ui.pic,ui.look,u.dept_id as deptId,d.`name` as deptName," +
                    "(select count(*) as total from user_focus where user_focus_id = ui.user_id) as follows " +
                    "from user_info ui " +
                    "left join `user` u on u.id = ui.user_id " +
                    "left join dept d on d.id = u.dept_id " +
                    "where ui.user_id = ?";

            map = qr.query(sql, new MapHandler(), 1);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println(map);
    }

    @Test
    public void test8() {
        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
        String sql = "select * from user where id = ?";
        Map<String, Object> query = null;
        try {

            query = queryRunner.query(sql, new MapHandler(), 1);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println(query);
    }

    @Test
    public void test9() {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        String sql = "select * from user";
        try {
            List<Map<String, Object>> query = qr.query(sql, new MapListHandler());
            System.out.println(query);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void findPageUser2() {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        StringBuilder sb = new StringBuilder();
        sb.append("select u.id,u.username,f.real_name,f.gender,f.age,f.describe\n" +
                "from user u\n" +
                "left join user_info f\n" +
                "on u.id = f.user_id\n");


        sb.append("where u.username like ? or f.real_name like ?");

        sb.append("order by u.id\n" +
                "limit ?,?");
        System.out.println(sb.toString());
        try {
            List<Map<String, Object>> list = qr.query(sb.toString(), new MapListHandler(), "小%", "小%", 1, 2);
            System.out.println(list);
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }


    @Test
    public void findUserFocus() {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        String sql = "select user_id, user_focus_id\n" +
                "from user_focus  where user_id = ?";

        try {
            List<UserFocus> query = qr.query(sql, new BeanListHandler<>(UserFocus.class), 1);
            System.out.println(query);
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    @Test
    public void UE() {
        String encode = URLEncoder.encode("你好！");
        System.out.println(encode);
        String decode = URLDecoder.decode(encode);

        System.out.println(decode);
    }

    @Test
    public void findList() {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        List<Map<String, Object>> maps = new ArrayList<>();

            StringBuilder sql = new StringBuilder();
            sql.append("select u.id as userId, ui.real_name as realName,");
            sql.append("ui.age,ui.gender,ui.describe as introduce,u.username,uf.id as ufId ");
            sql.append("from user_info ui ");
            sql.append("left join user u on ui.user_id = u.id ");
            sql.append("left join user_focus uf on uf.user_focus_id = u.id and uf.user_id = ? ");

            sql.append("order by u.id limit ?,? ");


            sql.append("where ui.real_name like ? or u.username like ? ");
            sql.append("order by u.id limit ?,? ");
        System.out.println(sql.toString());


    }
    @Test
    public void Test10() throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        String sql = "select u.id,u.username,f.age,f.real_name,f.phone,f.gender,f.describe,fo.user_focus_id\n" +
                "from user u\n" +
                "left join user_info f\n" +
                "on u.id = f.user_id\n" +
                "left join user_focus fo\n" +
                "on u.id = fo.user_focus_id and fo.user_id = 1\n" +
                "where u.username like 'x%' or f.real_name like 'x%'\n" +
                "order by u.id\n" +
                "limit 0,3";
        Map<String, Object> query = qr.query(sql, new MapHandler());
        System.out.println(query);

    }
//[Dept{id=1, name='研发部'}, Dept{id=2, name='推广部'}, Dept{id=3, name='行政部'},
    @Test
    public void Test11() throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        BasicRowProcessor bp = new BasicRowProcessor(new GenerousBeanProcessor());
        String sql = "select * from dept;";
        List<Dept> query = qr.query(sql, new BeanListHandler<>(Dept.class, bp));
        System.out.println(query);

    }
//[{id=1, name=研发部}, {id=2, name=推广部}, {id=3, name=行政部}, {id=4, name=财务部}, {id=5, name=总裁办公室},
    @Test
    public void Test12() throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        BasicRowProcessor bp = new BasicRowProcessor(new GenerousBeanProcessor());
        String sql = "select * from dept;";
        List<Map<String, Object>> query = qr.query(sql, new MapListHandler());
        System.out.println(query);

    }


    @Test
    public void test13(){

        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        String sql = "select d.*,i.real_name\n" +
                "from dept d\n" +
                "left join user u on d.id=u.dept_id\n" +
                "left join user_info i on u.id = i.user_id ";
        try {
            ArrayList<Map<String,Object>> list = new ArrayList<>();
//            set用来存放不重复的部门名字
            HashSet<String> set = new HashSet<>();
            List<Map<String, Object>> query = qr.query(sql, new MapListHandler());
            for (Map<String, Object> o : query) {
                String deptName = o.get("name").toString();
                Object realName = o.get("real_name");
                if (!set.contains(deptName)){
                    set.add(deptName);
//                    创建一个部门对象map
                    HashMap<String, Object> map = new HashMap<>();
//                    创建一个成员名字list
                    ArrayList<String> memberNames = new ArrayList<>();
                    map.put("deptName",deptName);
                    if (realName!=null){
                        map.put("count",1);
                        memberNames.add(realName.toString());
                        map.put("memberNames",memberNames);
                    }else {
                        map.put("count",0);
                        memberNames.add(null);
                        map.put("memberNames",memberNames);
                    }
                    list.add(map);
                }else {
//                    也就是说不是第一次加入这个部门，遍历list来拿到对应的map对象，修改count
                    for (Map<String, Object> map1 : list) {
                        if (map1.get("deptName").toString().equals(deptName)){
                            String count = map1.get("count").toString();
                            ArrayList<String> memberNames = (ArrayList<String>)map1.get("memberNames");
                            memberNames.add(realName.toString());
                            int i = Integer.parseInt(count);
//                            部门名字重复了，说明该部门肯定至少多于一个人
//                            你这样put进去就是改变了这个map对象了吗？
                            map1.put("count",++i);
                        }
                    }
                }
               set.add(o.get("name").toString());
            }
            System.out.println(set);
            System.out.println(list);


        } catch (SQLException e) {
            e.printStackTrace();
        }


//        HashMap<String, String> depMap = new HashMap<>();
//        ArrayList<String> names = new ArrayList<>();
//        depMap.put("dept","部门一名称");
//        names.add("小东");
//        names.add("小明");
//        HashMap<String, Integer> count = new HashMap<>();
//        count.put("count",3);
//        ArrayList<Object> list = new ArrayList<>();
//        list.add(depMap);
//        list.add(names);
//        list.add(count);
//        System.out.println(list);
//        for (Object o : list) {
//            System.out.println(o);
//        }
    }

    @Test
    public void test14(){
        HashMap<Object, Object> map = new HashMap<>();
        map.put("aa",11);
        map.put("aa",22);
        map.put("aa",11);
        map.put("bb",22);
        System.out.println(map.get("aa"));
        System.out.println(map.size());
    }
}
